import pymysql
import json

def get_num_periods(lottery_type,state):
    # 创建数据库连接
    db = pymysql.connect(host='192.168.31.235',  # 数据库地址
                         user='root',  # 数据库用户名
                         password='DC@72mon',  # 数据库密码
                         database='homedata',  # 要连接的数据库名
                         charset='utf8mb4',  # 字符集
                         cursorclass=pymysql.cursors.DictCursor)  # 使用字典游标
    num_periods = ""
    try:
        with db.cursor() as cursor:

            sql = "select distinct num_periods from lottery.lottery_state a where a.state = "+ str(state) +" and lottery_type = '" + lottery_type + "';"
            cursor.execute(sql)
            result = cursor.fetchall()  # 获取所有查询结果
            # print(len(result))
            for row in result:
                # print(row)
                num_periods = row["num_periods"]
    finally:
        db.close()  # 关闭数据库连接

    return num_periods


def get_items_by_name(name):
    # 创建数据库连接
    db = pymysql.connect(host='192.168.31.235',  # 数据库地址
                         user='root',  # 数据库用户名
                         password='DC@72mon',  # 数据库密码
                         database='homedata',  # 要连接的数据库名
                         charset='utf8mb4',  # 字符集
                         cursorclass=pymysql.cursors.DictCursor)  # 使用字典游标
    result = []
    try:
        with db.cursor() as cursor:

            sql = "select  id,item_name, item_urllist, item_amount, item_overtime, item_status, item_categroy, item_use,item_position, postition_urllist, remark from items a where a.item_name like '%" + name + "%';"
            # sql = "select  store_name,region1,region2 from storehouse a where a.store_name like '%" + name + "%';"
            print(sql)
            cursor.execute(sql)
            result = cursor.fetchall()  # 获取所有查询结果
            # return result
            # print(result)
            resp = []
            for row in result:
                tt_row = {}
                print(row)
                tt_row['id'] = row['id']
                tt_row['item_name'] = row['item_name']
                tt_row['item_urllist'] = row['item_urllist'].split(',')
                tt_row['item_amount'] = row['item_amount']
                tt_row['item_overtime'] = row['item_overtime']
                tt_row['item_status'] = row['item_status']
                tt_row['item_categroy'] = row['item_categroy']
                tt_row['item_use'] = row['item_use']
                tt_row['item_position'] = row['item_position']
                tt_row['postition_urllist'] = row['postition_urllist'].split(',')
                tt_row['remark'] = row['remark']
                resp.append(tt_row)
                # print(resp)
                # json_string = json.dumps(row)
                # print(json_string)
                # for key in row.keys():
                #     num.append(row[key])
                # print(num)
    finally:
        db.close()  # 关闭数据库连接

    return resp
    # return result


def get_items_by_id(id):
    # 创建数据库连接
    db = pymysql.connect(host='192.168.31.235',  # 数据库地址
                         user='root',  # 数据库用户名
                         password='DC@72mon',  # 数据库密码
                         database='homedata',  # 要连接的数据库名
                         charset='utf8mb4',  # 字符集
                         cursorclass=pymysql.cursors.DictCursor)  # 使用字典游标
    result = []
    try:
        with db.cursor() as cursor:

            sql = "select  id,item_name, item_urllist, item_amount, item_overtime, item_status, item_categroy, item_use,item_position, postition_urllist, remark from items a where a.id = '" + id + "';"
            # sql = "select  store_name,region1,region2 from storehouse a where a.store_name like '%" + name + "%';"
            print(sql)
            cursor.execute(sql)
            result = cursor.fetchall()  # 获取所有查询结果
            # return result
            # print(result)
            resp = []
            for row in result:
                tt_row = {}
                print(row)
                tt_row['id'] = row['id']
                tt_row['item_name'] = row['item_name']
                tt_row['item_urllist'] = row['item_urllist'].split(',')
                tt_row['item_amount'] = row['item_amount']
                tt_row['item_overtime'] = row['item_overtime']
                tt_row['item_status'] = row['item_status']
                tt_row['item_categroy'] = row['item_categroy']
                tt_row['item_use'] = row['item_use']
                tt_row['item_position'] = row['item_position']
                tt_row['postition_urllist'] = row['postition_urllist'].split(',')
                tt_row['remark'] = row['remark']
                resp.append(tt_row)
                # print(resp)
                # json_string = json.dumps(row)
                # print(json_string)
                # for key in row.keys():
                #     num.append(row[key])
                # print(num)
    finally:
        db.close()  # 关闭数据库连接

    return resp
    # return result



def save_mysql_lottery (sql,values):
    # 创建数据库连接
    db = pymysql.connect(host='192.168.31.235',  # 数据库地址
                         user='root',  # 数据库用户名
                         password='DC@72mon',  # 数据库密码
                         database='homedata',  # 要连接的数据库名
                         charset='utf8mb4',  # 字符集
                         cursorclass=pymysql.cursors.DictCursor)  # 使用字典游标
    lotteryDrawResult = []
    try:
        with db.cursor() as cursor:
            cursor.execute(sql, values)
            db.commit()  # 提交事务
    finally:
        db.close()  # 关闭数据库连接


if __name__ == "__main__":
    get_storehouse_mysql("asdf")